﻿<%@ WebHandler Language="C#" Class="WorkPrepareDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/**
 * Description: 生产准备看板
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.7.13
 **/

public class WorkPrepareDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getTouliaoInfo":
                        r = getTouliaoInfo(jObject);//分厂投料情况
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getGongyiZhunBei":
                        r = getGongyiZhunBei(jObject);//工艺准备情况
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTool":
                        r = getTool(jObject);//工装工具准备
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkcenterPrepare":
                        r = getWorkcenterPrepare(jObject);//各工区工装准备情况
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //分厂投料情况
    Result getTouliaoInfo(JObject jObject)
    {
        StringBuilder sb = new StringBuilder();
        // 查询分厂下所有生产工区
        sb.AppendLine("SELECT w.workcentername");
        sb.AppendLine("FROM factory f");
        sb.AppendLine("LEFT JOIN Workcenter w ON w.factoryid = f.factoryid");
        sb.AppendLine("WHERE ((w.wctype IS NULL) OR (w.wctype = 0)) AND w.notes IS NULL ");
        if (jObject["factoryid"] != null)
        {
            sb.AppendFormat(" AND f.factoryid = '{0}'", jObject["factoryid"]);
        }
        DataTable dtWorkcenter = dal.GetDataTable(sb.ToString());

        // 查询分厂各生产工区"未到料"批次数
        sb = new StringBuilder();
        sb.AppendLine("SELECT cci.fromworkcentername,COUNT(DISTINCT cci.togetherflag) notDaoliaoQty");
        sb.AppendLine("FROM containercurrentinfo cci ");
        sb.AppendLine("WHERE cci.isselfmfg = 0 AND cci.isinstore = -1 AND cci.containerstatus = 1 AND cci.workflowstepname <> '工人领料'");
        if (jObject["factoryid"] != null)
        {
            sb.AppendFormat(" AND cci.factoryid = '{0}'", jObject["factoryid"]);
        }
        sb.AppendLine("GROUP BY cci.fromworkcentername");

        DataTable dtNotDaoliaoInfo = dal.GetDataTable(sb.ToString());

        // 查询分厂各生产工区 "已到料"批次数
        sb = new StringBuilder();
        sb.AppendLine("SELECT cci.fromworkcentername,COUNT(DISTINCT cci.togetherflag) daoliaoQty");
        sb.AppendLine("FROM containercurrentinfo cci ");
        sb.AppendLine("WHERE cci.isselfmfg = 0 AND cci.isinstore = -1 AND cci.containerstatus = 1 AND cci.workflowstepname = '工人领料'");
        if (jObject["factoryid"] != null)
        {
            sb.AppendFormat(" AND cci.factoryid = '{0}'", jObject["factoryid"]);
        }
        sb.AppendLine("GROUP BY cci.fromworkcentername");

        DataTable dtDaoliaoInfo = dal.GetDataTable(sb.ToString());

        dtWorkcenter.Columns.Add("notDaoliaoQty", typeof(Int32));// 未到料批次数
        dtWorkcenter.Columns.Add("DaoliaoQty", typeof(Int32));  // 已到料批次数

        foreach (DataRow dr in dtWorkcenter.Rows)
        {
            string filter = string.Format("fromworkcentername = '{0}'", dr["workcentername"]);
            DataRow[] drNotDaoliao = dtNotDaoliaoInfo.Select(filter);
            DataRow[] drDaoliao = dtDaoliaoInfo.Select(filter);

            if (drNotDaoliao.Length > 0)
            {
                dr["notDaoliaoQty"] = Convert.ToInt32(drNotDaoliao[0]["notDaoliaoQty"]);
            }
            else
            {
                dr["notDaoliaoQty"] = 0;
            }

            if (drDaoliao.Length > 0)
            {
                dr["DaoliaoQty"] = Convert.ToInt32(drDaoliao[0]["daoliaoQty"]);
            }
            else
            {
                dr["DaoliaoQty"] = 0;
            }
        }

        foreach (DataColumn col in dtWorkcenter.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtWorkcenter;
        r.total = Convert.ToInt32(dtWorkcenter.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工艺准备
    Result getGongyiZhunBei(JObject jObject)
    {
        string sql = @"
SELECT up.description,up.ufccontent,w2.workcentername,e.fullname submitPerson,
       CASE WHEN up.ufcstatus=0 THEN '已完成' 
            WHEN up.ufcstatus=1 THEN '未处理' 
            WHEN up.ufcstatus=2 THEN '处理中' 
            WHEN up.ufcstatus=3 THEN '处理完' END status,
       ROUND((SYSDATE-up.ufcchulidate),2) AS delayday
FROM ufcproblem up
LEFT JOIN employee e ON e.employeeid = up.ufccreateemployeeid --问题提出人
LEFT JOIN Sessionvalues sv ON sv.employeeid = up.ufccreateemployeeid
LEFT JOIN workcenter w2 ON w2.workcenterid = sv.workcenterid  --提出单位
LEFT JOIN workcenter w ON w.workcenterid = up.workcenterid    --责任工区
WHERE up.ufcstatus <> '0' AND w.workcentername like '%技术质量处%'
";
        if (jObject["factoryid"] != null)
        {
            sql += string.Format(" AND up.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataRow dr in dt.Rows)
        {
            double delayday = Convert.ToDouble(dr["delayday"]);
            if (delayday < 0)
            {
                dr["delayday"] = "0";
            }
        }
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工装工具
    Result getTool(JObject jObject)
    {
        string sql = @"
SELECT cci.productname,cci.togetherflag,ws.sequence,ag.toolno,ag.toolname,ag.qty,ag.createdate,ag.needdate,w.workcentername,
       t.teamname,e.fullname,ag.toolstatus,DECODE(ag.toolstatus,'待准备',ROUND((SYSDATE-ag.needdate),1),0) delaydays
FROM arrangements ag
LEFT JOIN containercurrentinfo cci ON ag.containerid = cci.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = ag.workflowstepid
LEFT JOIN sessionvalues sv ON sv.employeeid = ag.employeeid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
LEFT JOIN team t ON t.teamid = ag.teamid
LEFT JOIN employee e ON ag.employeeid = e.employeeid
WHERE cci.isinstore = 0 AND cci.containerstatus = 1
";
        if (jObject["factoryid"] != null)
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataRow dr in dt.Rows)
        {
            double delayday = Convert.ToDouble(dr["delaydays"]);
            if (delayday < 0)
            {
                dr["delayday"] = "0";
            }
        }
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各工区工装准备情况
    Result getWorkcenterPrepare(JObject jObject)
    {
        StringBuilder sb = new StringBuilder();
        // 查询分厂下所有生产工区
        sb.AppendLine("SELECT w.workcentername");
        sb.AppendLine("FROM factory f");
        sb.AppendLine("LEFT JOIN Workcenter w ON w.factoryid = f.factoryid");
        sb.AppendLine("WHERE ((w.wctype IS NULL) OR (w.wctype = 0)) ");
        if (jObject["factoryid"] != null)
        {
            sb.AppendFormat(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        DataTable dtWorkcenter = dal.GetDataTable(sb.ToString());

        sb = new StringBuilder();
        sb.AppendLine("SELECT cci.fromworkcentername workcentername,CASE WHEN ag.toolstatus = '待准备' THEN  '待准备' ELSE '配送中' END  status");
        sb.AppendLine("FROM arrangements ag");
        sb.AppendLine("LEFT JOIN containercurrentinfo cci ON cci.containerid = ag.containerid");
        sb.AppendLine("WHERE cci.containerstatus = 1 ");
        if (jObject["factoryid"] != null)
        {
            sb.AppendFormat(" AND cci.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        DataTable dt = dal.GetDataTable(sb.ToString());

        dtWorkcenter.Columns.Add("daizhunbei", typeof(Int32));// 待准备列
        dtWorkcenter.Columns.Add("peisongzhong", typeof(Int32));// 准备中

        foreach (DataRow dr in dtWorkcenter.Rows)
        {
            string dzbFilter = string.Format("workcentername = '{0}' and status = '待准备'", dr["workcentername"]);
            string pszFilter = string.Format("workcentername = '{0}' and status = '配送中'", dr["workcentername"]);

            int daizhunbei = Convert.ToInt32(dt.Compute("Count(status)", dzbFilter));
            int peisongzhong = Convert.ToInt32(dt.Compute("Count(status)", pszFilter));

            dr["daizhunbei"] = daizhunbei;
            dr["peisongzhong"] = peisongzhong;
        }

        foreach (DataColumn col in dtWorkcenter.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtWorkcenter;
        r.total = Convert.ToInt32(dtWorkcenter.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}